IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncCfg2MSSQL' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncCfg2MSSQL
GO

CREATE PROCEDURE dbo.SaveSrvrInstncCfg2MSSQL
     @SrvrInstncId             int,
     @PackageExecutionId       bigint,
     @SysConfigurationId       int,
     @SysConfigurationName     varchar(255),
     @ConfigValue              bigint,
     @RunValue                 bigint
AS
/*
 *
 * SaveSrvrInstncCfg2MSSQL
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      Config deletes are not taken into account
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 12/05/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @IdForSrv    int,
             @StartDt     datetime,
             @RC          int,
             @NOW         datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncCfg2MSSQL
                     WHERE  SrvrInstncId        = @SrvrInstncId
                     AND    SysConfigurationId  = @SysConfigurationId )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          INSERT dbo.SrvrInstncCfg2MSSQL
               ( SrvrInstncId,
                 PackageExecutionId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 SysConfigurationId,
                 SysConfigurationName,
                 ConfigValue,
                 RunValue )
          SELECT @SrvrInstncId,
                 @PackageExecutionId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @SysConfigurationId,
                 @SysConfigurationName,
                 @ConfigValue,
                 @RunValue
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.SrvrInstncCfg2MSSQL
          WHERE  SrvrInstncId       = @SrvrInstncId
          AND    SysConfigurationId = @SysConfigurationId
          AND    CurrentRecordFlag  = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.SrvrInstncCfg2MSSQL
          SET    EndDt              = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag  = 0
          WHERE  SrvrInstncId       = @SrvrInstncId
          AND    SysConfigurationId = @SysConfigurationId
          AND    CurrentRecordFlag = 1

          -- Do regular insert
          INSERT dbo.SrvrInstncCfg2MSSQL
               ( SrvrInstncId,
                 PackageExecutionId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 SysConfigurationId,
                 SysConfigurationName,
                 ConfigValue,
                 RunValue )
          SELECT @SrvrInstncId,
                 @PackageExecutionId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @SysConfigurationId,
                 @SysConfigurationName,
                 @ConfigValue,
                 @RunValue
          FROM   dbo.SrvrInstncCfg2MSSQL
          WHERE  SrvrInstncId       = @SrvrInstncId
          AND    SysConfigurationId = @SysConfigurationId
          AND    StartDt            = @StartDt
          -- Decide if there will be an insert
          AND  ( @SysConfigurationName != SysConfigurationName
              OR @ConfigValue          != ConfigValue
              OR @RunValue             != RunValue
               ) -- End of And-Or section

          SELECT @RC = @@ROWCOUNT 

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO
